package com.edu.modules.baseinfo.dao;

import com.sari.common.persistence.BaseDao;
import com.sari.common.utils.DateUtils;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * Created by lab on 2016/7/15.
 */
@Repository
public class TeacherDao extends BaseDao {

	public List getTeacherByPage(String teacherName,String teacherPhone,
								 String subjectType,int offset,int limit){

		String sql = "select * from t_teacher where 1=1";
		if(!teacherName.equals("")){
			sql += " and teacher_name like '%"+teacherName+"%'";
		}
		if(!teacherPhone.equals("")){
			sql += " and teacher_phone like '%"+teacherPhone+"%'";
		}
		if (!subjectType.equals("")){
			sql += " and subject_type like '%"+subjectType+"%'";
		}
		sql +=" ORDER BY create_time DESC limit ?,?";

		return super.listBySql(sql,offset,limit);
	}

	public int getTeacherCounts(String teacherName,String teacherPhone,
								String subjectType){

		String sql = "select count(*) from t_teacher where 1=1";
		if(!teacherName.equals("")){
			sql += " and teacher_name like '%"+teacherName+"%'";
		}
		if(!teacherPhone.equals("")){
			sql += " and teacher_phone like '%"+teacherPhone+"%'";
		}
		if (!subjectType.equals("")){
			sql += " and subject_type like '%"+subjectType+"%'";
		}

		return super.listSqlCount(sql);
	}

	public List getTeacherLessonStatistics(String teacherName,String time,
										   int offset,int limit){

		String sql = "select count(*) AS counts, teacher_name from t_lesson_record where 1=1";
		if(teacherName.equals("")){
			sql += " GROUP BY teacher_name";
		}else{
			sql += " and teacher_name = '"+teacherName+"'";
		}
		if(!time.equals("")){
			sql += " and date_time like '%"+time+"%'";
		}
		sql +=" limit ?,?";

		return super.listBySql(sql,offset,limit);
	}

	public List getStudentCountsByTeacher(String teacherName,String grade){

		String sql = "SELECT a.teacher_name,a.lesson_class,a.lesson_start_time,a.lesson_name,a.sign_number " +
				     "FROM (SELECT * FROM t_lesson GROUP BY teacher_name,lesson_class) AS a WHERE 1=1";
		if(!teacherName.equals("")){
			sql += " AND a.teacher_name = '"+teacherName+"'";
		}
		if(!grade.equals("")){
			sql += " AND a.lesson_class = '"+grade+"'";
		}
		sql +=" AND a.start_date <= ?";

		return super.listBySql(sql,DateUtils.getDate());
	}

	public int getTeachersStudentCount(String teacherName,String grade,String startDate,String endDate){

		String sql = "SELECT SUM(a.sign_number) FROM t_lesson a WHERE 1=1";
		if(!teacherName.equals("")){
			sql += " AND a.teacher_name = '"+teacherName+"'";
		}
		if(!grade.equals("")){
			sql += " AND a.lesson_class = '"+grade+"'";
		}
		sql +=" AND a.start_date BETWEEN ? AND ? AND a.start_date <= ?";

		return super.getCount(sql,startDate,endDate, DateUtils.getDate());

	}

	public int getTeacherLessonStatisticsCount(String teacherName,String time){

		String sql = "select count(*) from t_lesson_record where 1=1";

		if(!teacherName.equals("")){
			sql += " and teacher_name = '"+teacherName+"'";
		}
		if(!time.equals("")){
			sql += " and date_time like '%"+time+"%'";
		}

		return super.listSqlCount(sql);
	}

	public List getTeacherName(){

		String sql = "select distinct teacher_name,subject_type from t_teacher";

		return super.listBySql(sql);
	}

	public List getTeachers(){

		String sql = "select distinct teacher_name from t_lesson";

		return super.listBySql(sql);
	}

	public List getTeachersLesson(){

		String sql = "select distinct subject_type from t_teacher";

		return super.listBySql(sql);
	}

	public List getTeacherByLesson(String lesson){

		String sql = "select distinct teacher_name from t_lesson where lesson_name = ?";

		return super.listBySql(sql,lesson);
	}

	public List getTeacherEvaluateByPage(String lessonName,String startTime,String endTime,int offset,int limit){

		String sql = "select * from t_lesson_evaluation where 1=1";
		if(!lessonName.equals("")){
			sql += " and lesson_name = '"+lessonName+"'";
		}
		if((!startTime.equals(""))&&(!endTime.equals(""))){
			sql += " and create_time between '"+startTime+"' and '"+endTime+"'";
		}

		sql +=" ORDER BY create_time DESC limit ?,?";

		return super.listBySql(sql,offset,limit);
	}

	public int getTeacherEvaluateCount(String lessonName,String startTime,String endTime){

		String sql = "select count(*) from t_lesson_evaluation where 1=1";

		if(!lessonName.equals("")){
			sql += " and lesson_name = '"+lessonName+"'";
		}
		if((!startTime.equals(""))&&(!endTime.equals(""))){
			sql += " and create_time between '"+startTime+"' and '"+endTime+"'";
		}

		return super.listSqlCount(sql);
	}
}
